package org.wjw.dao;

import org.wjw.model.Blog;
import org.wjw.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BlogDao {

    /**
     * 插入一条新的博客
     *
     * @param blog blog对象
     * @return 受影响的行数
     */
    public int insert(Blog blog) {
        if (blog == null) {
            return 0;
        }
        Connection connection = null;
        PreparedStatement statement = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "insert into blog values(null, ?, ?, ?, ?);";
            statement = connection.prepareStatement(sql);
            statement.setString(1, blog.getTitle());
            statement.setString(2, blog.getContent());
            statement.setTimestamp(3, blog.getCreateTime());
            statement.setLong(4, blog.getUserId());
            // 执行SQL 获取结果
            result = statement.executeUpdate();
            // 返回结果
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接并释放资源
            DBUtil.close(null, statement, connection);
        }
        return result;
    }

    /**
     * 按博客Id删除博客
     *
     * @param id 博客Id
     * @return 受影响的行数
     */
    public int delete(Long id) {
        // 非空校验
        if (id == null) {
            return 0;
        }
        Connection connection = null;
        PreparedStatement statement = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "delete from blog where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setLong(1, id);
            // 执行SQL 获取结果
            result = statement.executeUpdate();
            // 返回结果
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭数据库连接并释放资源
            DBUtil.close(null, statement, connection);
        }
        return result;
    }


    /**
     * 按Id查询博客详情
     *
     * @param id 博客Id
     * @return blog对象
     */
    public Blog selectById(Long id) {
        // 全法性校验
        if (id == null || id <= 0) {
            return null;
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            // 1. 获取数据库连接
            connection = DBUtil.getConnection();
            // 2. 构造SQL语句
            String sql = "select id, title, content, createTime, userId from blog where id = ?";
            // 3. 获取预处理对象
            statement = connection.prepareStatement(sql);
            // 替换占位符
            statement.setLong(1, id);
            // 4. 提交执行SQL并获取结果
            resultSet = statement.executeQuery();
            // 5. 处理结果
            if (resultSet.next()) {
                Blog blog = new Blog();
                blog.setId(resultSet.getLong("id"));
                blog.setTitle(resultSet.getString("title"));
                blog.setContent(resultSet.getString("content"));
                blog.setCreateTime(resultSet.getTimestamp("createTime"));
                blog.setUserId(resultSet.getLong("userId"));
                return blog;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭数据库连接，释放资源
            DBUtil.close(resultSet, statement, connection);
        }
        // 如果没有查询到结果，返回null
        return null;
    }

    /**
     * 查询所有博客
     *
     * @return List<Blog>
     */
    public List<Blog> selectAll() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        // 返回的集合
        List<Blog> result = null;

        try {
            // 1. 获取数据库连接
            connection = DBUtil.getConnection();
            // 2. 构造SQL语句
            String sql = "select id, title, content, createTime, userId from blog order by id desc";
            // 3. 获取预处理对象
            statement = connection.prepareStatement(sql);
            // 4. 提交执行SQL并获取结果
            resultSet = statement.executeQuery();
            // 5. 处理结果
            while (resultSet.next()) {
                if (result == null) {
                    result = new ArrayList<>();
                }
                Blog blog = new Blog();
                blog.setId(resultSet.getLong("id"));
                blog.setTitle(resultSet.getString("title"));
                blog.setContent(resultSet.getString("content"));
                blog.setCreateTime(resultSet.getTimestamp("createTime"));
                blog.setUserId(resultSet.getLong("userId"));
                // 加入集合中
                result.add(blog);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭数据库连接，释放资源
            DBUtil.close(resultSet, statement, connection);
        }
        // 如果没有查询到结果，返回null
        return result;
    }


}
